﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IWS.SiteManager.Core.Model;
using System.Text.RegularExpressions;
using System.Transactions;
using MySql.Data.MySqlClient;

namespace IWS.SiteManager.Core
{
	public class DBFactory
	{
		public const int VERSION = 1;

		public static Model.SiteManager GetProvider()
		{
			var con_string = GetDefaultConnectionString();
			return new Model.SiteManager(new MySql.Data.MySqlClient.MySqlConnection(con_string));
		}
		public static bool SqlServerDatabaseExists(string connection)
		{
			////check for our system table.. if it does not exist, it assumes this is a new empty datbase. 
			//using (MySqlConnection conn = new MySqlConnection(connection))
			//{
			//   //look for the system table. 
			//   string sql = "SELECT count(*) as qty from sysobjects where xtype = 'U' and name = 'SiteManagerSystem'";
			//   var cmd = new MySqlCommand(sql, conn);
			//   conn.Open();
			//   int? count = cmd.ExecuteScalar() as int?;
			//   conn.Close(); //redundant

			//   return count.HasValue && count.Value > 0;
			//}

			return false; //TODO: finish database exists check.
		}

		public static void CheckDatabaseVersion(string connection)
		{
			if (!SqlServerDatabaseExists(connection))
				RunVersion1Install(connection);

			//TODO: this is a work in progres... I'm toying with ways to auto-update data schema. 

			//SiteManagerDataContext db = new SiteManagerDataContext(connection);

			//var sys = db.SiteManagerSystems.SingleOrDefault();

			//bool success = true;
			//if (sys == null) //new install
			//   success = RunVersion1(connection);


			////these should waterfall.  when version 2 is ready. case 2 will start immediately after. so version 1, 2, 3, etc will happen in sequence
			//switch (sys.Version)
			//{
			//   case 1:
			//      success = RunVersion1(connection);
			//      if (!success)
			//         return success;
			//   //placeholder for case 2
			//      return success;
			//}

			//return success;
		}

		public static void RunVersion1Install(string connection)
		{
			using (MySqlConnection conn = new MySqlConnection(connection))
			{
				conn.Open();
				using (MySqlTransaction tran = conn.BeginTransaction())
				{
					//install schema and data

					MySqlCommand cmd = new MySqlCommand(DBScripts.version1_schema_and_data, conn, tran);
					cmd.CommandType = System.Data.CommandType.Text;
					cmd.ExecuteNonQuery();

					tran.Commit();
				}
				conn.Close();
			}
		}

		public static string GetDefaultConnectionString()
		{
			var name = System.Configuration.ConfigurationManager.AppSettings["DefaultConnectionString"];
			var conn = System.Configuration.ConfigurationManager.ConnectionStrings[name];

			if (conn != null)
				return conn.ConnectionString;

			return null;
		}
	}
}